﻿Clear-Host;

$path= 'D:\MSSQL11.MSSQLSERVER\MSSQL\DATA';
$table= 'FactBBA_Consolidation';
$from= 2011;
$thru= 2019;

$pf_tsql= 'CREATE PARTITION FUNCTION pf_TimeID (NVARCHAR(20)) AS RANGE FOR VALUES (';
$ps_tsql= 'CREATE PARTITION SCHEME ps_TimeID AS PARTITION pf_TimeID TO (';
for ($year= $from; $year -le $thru; $year++) {
	$pf_tsql+= "`n"
	$ps_tsql+= "`n"
	for ($month= 1; $month -le 12 ; $month++) {
		$boundary_value= "$year$(($month.ToString()).PadLeft(2,'0'))";
		Write-Output "ALTER DATABASE BBA ADD FILEGROUP [$table`_$boundary_value]; ALTER DATABASE BBA ADD FILE (NAME= '$table`_$boundary_value', FILENAME= '$path\$table`_$boundary_value.ndf', SIZE= 64MB, FILEGROWTH= 64MB) TO FILEGROUP [$table`_$boundary_value];"
		$pf_tsql+=  "N'$(($boundary_value.ToString()).PadRight(8,'0'))',";
		$ps_tsql+= "[$table`_$boundary_value],";
#	#	New-Item -Path $Path -name "fg_$boundary_value" -ItemType Directory;
	};
};

Write-Output "$($pf_tsql.Substring(0, $pf_tsql.Length-1)));";
Write-Output "$ps_tsql[PRIMARY]);";
